{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd ,xlwings as xw,numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "基础数据path=r'J:\\王振洋资料\\2.商贸分公司资料\\t+基础数据金水分.xlsx'\n",
    "\n",
    "销售出库单p=r'J:\\王振洋资料\\2.商贸分公司资料\\商贸分导入模板\\销售出库单.xlsx'\n",
    "\n",
    "凭证导入导出p=r'J:\\王振洋资料\\2.商贸分公司资料\\商贸分导入模板\\凭证导入导出.xlsx'\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "销售台账path = r'j:\\王振洋资料\\2.商贸分公司资料\\9月商贸分公司资料\\5.技术代收商贸分\\技术代收代付汇总9月.xlsx'\n",
    "wb_台账=xw.Book(销售台账path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 获取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "台账data = wb_台账.sheets('9月').range('a2').expand('table').options(pd.DataFrame,index=False).value\n",
    "# 台账data=台账data.loc[:,['时间','城市','物料名称','数量','单价','总价2']]  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>账户</th>\n",
       "      <th>时间</th>\n",
       "      <th>对方账户</th>\n",
       "      <th>对方名称</th>\n",
       "      <th>收入金额</th>\n",
       "      <th>支出金额</th>\n",
       "      <th>sheet来源</th>\n",
       "      <th>物料类型</th>\n",
       "      <th>台账日期</th>\n",
       "      <th>台账城市</th>\n",
       "      <th>备注</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>余额支付</td>\n",
       "      <td>2024-09-02 09:45:46</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>630.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-03</td>\n",
       "      <td>固始</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>余额支付</td>\n",
       "      <td>2024-09-02 21:34:22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>375.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-03</td>\n",
       "      <td>衡水</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>余额支付</td>\n",
       "      <td>2024-09-06 13:58:54</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>250.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-06</td>\n",
       "      <td>绍兴</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>余额支付</td>\n",
       "      <td>2024-09-15 18:30:08</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>66.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-16</td>\n",
       "      <td>绍兴</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>余额支付</td>\n",
       "      <td>2024-09-15 18:32:20</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>294.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-16</td>\n",
       "      <td>绍兴</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>银行转账</td>\n",
       "      <td>2024-09-18 00:00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3720.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-27</td>\n",
       "      <td>芒市</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>银行转账</td>\n",
       "      <td>2024-09-11 00:00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-949.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>汾西</td>\n",
       "      <td>多余物料款退回</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>银行转账</td>\n",
       "      <td>2024-09-07 00:00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-203.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月家政物料</td>\n",
       "      <td>家政物料</td>\n",
       "      <td>2024-09-07</td>\n",
       "      <td>郑州</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>银行转账</td>\n",
       "      <td>2024-09-11 00:00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-1008.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-11</td>\n",
       "      <td>唐河</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>银行转账</td>\n",
       "      <td>2024-09-11 00:00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-1934.0</td>\n",
       "      <td>None</td>\n",
       "      <td>9月跑腿物料</td>\n",
       "      <td>跑腿物料</td>\n",
       "      <td>2024-09-11</td>\n",
       "      <td>汾西</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      账户                  时间  对方账户  对方名称    收入金额  支出金额 sheet来源  物料类型  \\\n",
       "0   余额支付 2024-09-02 09:45:46  None  None   630.0  None  9月跑腿物料  跑腿物料   \n",
       "1   余额支付 2024-09-02 21:34:22  None  None   375.0  None  9月跑腿物料  跑腿物料   \n",
       "2   余额支付 2024-09-06 13:58:54  None  None   250.0  None  9月跑腿物料  跑腿物料   \n",
       "3   余额支付 2024-09-15 18:30:08  None  None    66.0  None  9月跑腿物料  跑腿物料   \n",
       "4   余额支付 2024-09-15 18:32:20  None  None   294.0  None  9月跑腿物料  跑腿物料   \n",
       "..   ...                 ...   ...   ...     ...   ...     ...   ...   \n",
       "60  银行转账 2024-09-18 00:00:00  None  None  3720.0  None  9月跑腿物料  跑腿物料   \n",
       "61  银行转账 2024-09-11 00:00:00  None  None  -949.0  None    None  None   \n",
       "62  银行转账 2024-09-07 00:00:00  None  None  -203.0  None  9月家政物料  家政物料   \n",
       "63  银行转账 2024-09-11 00:00:00  None  None -1008.0  None  9月跑腿物料  跑腿物料   \n",
       "64  银行转账 2024-09-11 00:00:00  None  None -1934.0  None  9月跑腿物料  跑腿物料   \n",
       "\n",
       "         台账日期 台账城市       备注  \n",
       "0  2024-09-03   固始     None  \n",
       "1  2024-09-03   衡水     None  \n",
       "2  2024-09-06   绍兴     None  \n",
       "3  2024-09-16   绍兴     None  \n",
       "4  2024-09-16   绍兴     None  \n",
       "..        ...  ...      ...  \n",
       "60 2024-09-27   芒市     None  \n",
       "61        NaT   汾西  多余物料款退回  \n",
       "62 2024-09-07   郑州     None  \n",
       "63 2024-09-11   唐河     None  \n",
       "64 2024-09-11   汾西     None  \n",
       "\n",
       "[65 rows x 11 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "台账data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 编码匹配"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb基础数据=xw.Book(基础数据path)\n",
    "仓库档案=wb基础数据.sheets('仓库档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "部门档案=wb基础数据.sheets('部门档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','收入科目编码','收入科目名称']]\n",
    "往来单位档案=wb基础数据.sheets(\"往来单位匹配汇总\").range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "\n",
    "合并=pd.merge(台账data,往来单位档案,left_on='台账城市',right_on='城市',how='left')\n",
    "合并['部门编码']='03'\n",
    "合并['部门']='招商部'\n",
    "合并['时间']=合并['时间'].dt.strftime('%Y-%m-%d')  #修改时间格式\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将数据写入编码匹配中间表\n",
    "\n",
    "wb_台账.sheets('编码匹配中间表').cells.clear_contents() \n",
    "wb_台账.sheets('编码匹配中间表').cells.number_format='@'\n",
    "wb_台账.sheets('编码匹配中间表').range('a1').value=合并 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 模版数据源 与 销售出库单、凭证导入导出模版"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 检查是否有销售出库单和凭证导入导出，如果没有，则打开并新建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheetname = wb_台账.sheet_names\n",
    "\n",
    "\n",
    "    \n",
    "   \n",
    "\n",
    "\n",
    "if '凭证导入导出' not in sheetname:\n",
    "    凭证导入导出=xw.Book(凭证导入导出p)\n",
    "    凭证导入导出.sheets('凭证导入导出').copy(after=wb_台账.sheets[wb_台账.sheets.count-1])\n",
    "    凭证导入导出.close()\n",
    "else:\n",
    "    None\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据写入到凭证导入导出"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在模版数据源中，添加 不含税金额、税额两列、修改总价为合计金额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "ename": "com_error",
     "evalue": "(-2147352567, '发生意外。', (0, None, None, None, 0, -2147352565), None)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mcom_error\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_28604\\2506672446.py\u001b[0m in \u001b[0;36m?\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 读取df\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0m数据源\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mwb_台账\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msheets\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'2.编码匹配中间表'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'a1'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexpand\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0m凭证模版\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mwb_台账\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msheets\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'凭证导入导出'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;34m\"\"\"出库单模版=wb_台账.sheets(\"销售出库单\")\"\"\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\xlwings\\main.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, name_or_index)\u001b[0m\n\u001b[0;32m   5066\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__call__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5067\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_or_index\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mSheet\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5068\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mname_or_index\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5069\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5070\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mSheet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mimpl\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mimpl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\xlwings\\_xlwindows.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, name_or_index)\u001b[0m\n\u001b[0;32m    894\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__call__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 895\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mSheet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mxl\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mxl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\xlwings\\_xlwindows.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m    233\u001b[0m                 ) and e.hresult == -2147418111:\n\u001b[0;32m    234\u001b[0m                     \u001b[0mn_attempt\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    235\u001b[0m                     \u001b[1;32mcontinue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    236\u001b[0m                 \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 237\u001b[1;33m                     \u001b[1;32mraise\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    238\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    239\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mN_COM_ATTEMPTS\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mn_attempt\u001b[0m \u001b[1;33m<\u001b[0m \u001b[0mN_COM_ATTEMPTS\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    240\u001b[0m                     \u001b[0mn_attempt\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\win32com\\gen_py\\00020813-0000-0000-C000-000000000046x0x1x9.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, Index)\u001b[0m\n\u001b[0;32m  36706\u001b[0m         \u001b[1;32mdef\u001b[0m \u001b[0m__call__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mIndex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdefaultNamedNotOptArg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m> 36707\u001b[1;33m \t\tret = self._oleobj_.InvokeTypes(0, LCID, 2, (9, 0), ((12, 1),),Index\n\u001b[0m\u001b[0;32m  36708\u001b[0m \t\t\t)\n\u001b[0;32m  36709\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mret\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m  36710\u001b[0m                         \u001b[0mret\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mDispatch\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mret\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'__call__'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mcom_error\u001b[0m: (-2147352567, '发生意外。', (0, None, None, None, 0, -2147352565), None)"
     ]
    }
   ],
   "source": [
    "# 读取df\n",
    "数据源=wb_台账.sheets('2.编码匹配中间表').range('a1').expand().options(pd.DataFrame).value\n",
    "凭证模版=wb_台账.sheets('凭证导入导出')\n",
    "\"\"\"出库单模版=wb_台账.sheets(\"销售出库单\")\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 按组添加往来科目和销项税科目(df):\n",
    "    df=df.reset_index(drop=True)\n",
    "    # 应收账款的数据\n",
    "    应收金额=df['合计金额'].sum()\n",
    "    往来单位编码=df.loc[0,'往来单位编码'] #读取往来单位编码数据\n",
    "    往来单位名称=df.loc[0,'往来单位'] #读取往来单位的名称\n",
    "    data={'科目编码':'2203','科目':'预收账款','币种':'人民币','借贷方向':'借方','本币':应收金额,'往来单位编码':往来单位编码,'往来单位':往来单位名称,'部门编码':'','部门':''}\n",
    "    应收数据=pd.DataFrame(data,index=[0])\n",
    "    \n",
    "    # 收入的数据\n",
    "    收入数据=df.loc[:,['科目编码','科目','不含税金额','往来单位编码','往来单位','部门编码','部门',]]\n",
    "    收入数据['币种']='人民币'\n",
    "    收入数据['借贷方向']='贷方'\n",
    "    收入数据['往来单位编码']=''\n",
    "    收入数据['往来单位']=''\n",
    "    收入数据['本币']=收入数据['不含税金额']\n",
    "    收入数据=收入数据.loc[:,['科目编码','科目','币种','借贷方向','本币','往来单位编码','往来单位','部门编码','部门']]\n",
    "\n",
    "    # 销项税的数据\n",
    "    销项金额=df['税额'].sum()\n",
    "    data={'科目编码':'22210106','科目':'销项税额','币种':'人民币','借贷方向':'贷方','本币':销项金额,'往来单位编码':'','往来单位':'','部门编码':'','部门':''}\n",
    "    销项数据=pd.DataFrame(data,index=[1])\n",
    "\n",
    "    合并=pd.concat([应收数据,收入数据,销项数据])\n",
    "    合并['备注']=df.loc[0,'备注']\n",
    "    return 合并   \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'date'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[23], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m 凭证导入导出\u001b[38;5;241m=\u001b[39m数据源\u001b[38;5;241m.\u001b[39mgroupby(by\u001b[38;5;241m=\u001b[39m[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdate\u001b[39m\u001b[38;5;124m'\u001b[39m])\u001b[38;5;241m.\u001b[39mapply(按组添加往来科目和销项税科目)  \n\u001b[0;32m      2\u001b[0m 凭证导入导出[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m原币\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m=\u001b[39m凭证导入导出[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m本币\u001b[39m\u001b[38;5;124m'\u001b[39m]\n\u001b[0;32m      3\u001b[0m 凭证导入导出\u001b[38;5;241m=\u001b[39m凭证导入导出\u001b[38;5;241m.\u001b[39mreset_index()\n",
      "File \u001b[1;32me:\\anaconda\\Lib\\site-packages\\pandas\\core\\frame.py:8252\u001b[0m, in \u001b[0;36mDataFrame.groupby\u001b[1;34m(self, by, axis, level, as_index, sort, group_keys, observed, dropna)\u001b[0m\n\u001b[0;32m   8249\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mYou have to supply one of \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mby\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m and \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlevel\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   8250\u001b[0m axis \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_axis_number(axis)\n\u001b[1;32m-> 8252\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m DataFrameGroupBy(\n\u001b[0;32m   8253\u001b[0m     obj\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m   8254\u001b[0m     keys\u001b[38;5;241m=\u001b[39mby,\n\u001b[0;32m   8255\u001b[0m     axis\u001b[38;5;241m=\u001b[39maxis,\n\u001b[0;32m   8256\u001b[0m     level\u001b[38;5;241m=\u001b[39mlevel,\n\u001b[0;32m   8257\u001b[0m     as_index\u001b[38;5;241m=\u001b[39mas_index,\n\u001b[0;32m   8258\u001b[0m     sort\u001b[38;5;241m=\u001b[39msort,\n\u001b[0;32m   8259\u001b[0m     group_keys\u001b[38;5;241m=\u001b[39mgroup_keys,\n\u001b[0;32m   8260\u001b[0m     observed\u001b[38;5;241m=\u001b[39mobserved,\n\u001b[0;32m   8261\u001b[0m     dropna\u001b[38;5;241m=\u001b[39mdropna,\n\u001b[0;32m   8262\u001b[0m )\n",
      "File \u001b[1;32me:\\anaconda\\Lib\\site-packages\\pandas\\core\\groupby\\groupby.py:931\u001b[0m, in \u001b[0;36mGroupBy.__init__\u001b[1;34m(self, obj, keys, axis, level, grouper, exclusions, selection, as_index, sort, group_keys, observed, dropna)\u001b[0m\n\u001b[0;32m    928\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdropna \u001b[38;5;241m=\u001b[39m dropna\n\u001b[0;32m    930\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m grouper \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m--> 931\u001b[0m     grouper, exclusions, obj \u001b[38;5;241m=\u001b[39m get_grouper(\n\u001b[0;32m    932\u001b[0m         obj,\n\u001b[0;32m    933\u001b[0m         keys,\n\u001b[0;32m    934\u001b[0m         axis\u001b[38;5;241m=\u001b[39maxis,\n\u001b[0;32m    935\u001b[0m         level\u001b[38;5;241m=\u001b[39mlevel,\n\u001b[0;32m    936\u001b[0m         sort\u001b[38;5;241m=\u001b[39msort,\n\u001b[0;32m    937\u001b[0m         observed\u001b[38;5;241m=\u001b[39mobserved,\n\u001b[0;32m    938\u001b[0m         dropna\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdropna,\n\u001b[0;32m    939\u001b[0m     )\n\u001b[0;32m    941\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj \u001b[38;5;241m=\u001b[39m obj\n\u001b[0;32m    942\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39maxis \u001b[38;5;241m=\u001b[39m obj\u001b[38;5;241m.\u001b[39m_get_axis_number(axis)\n",
      "File \u001b[1;32me:\\anaconda\\Lib\\site-packages\\pandas\\core\\groupby\\grouper.py:985\u001b[0m, in \u001b[0;36mget_grouper\u001b[1;34m(obj, key, axis, level, sort, observed, validate, dropna)\u001b[0m\n\u001b[0;32m    983\u001b[0m         in_axis, level, gpr \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m, gpr, \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[0;32m    984\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m--> 985\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(gpr)\n\u001b[0;32m    986\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(gpr, Grouper) \u001b[38;5;129;01mand\u001b[39;00m gpr\u001b[38;5;241m.\u001b[39mkey \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    987\u001b[0m     \u001b[38;5;66;03m# Add key to exclusions\u001b[39;00m\n\u001b[0;32m    988\u001b[0m     exclusions\u001b[38;5;241m.\u001b[39madd(gpr\u001b[38;5;241m.\u001b[39mkey)\n",
      "\u001b[1;31mKeyError\u001b[0m: 'date'"
     ]
    }
   ],
   "source": [
    "凭证导入导出=数据源.groupby(by=['合并标识']).apply(按组添加往来科目和销项税科目)  \n",
    "凭证导入导出['原币']=凭证导入导出['本币']\n",
    "凭证导入导出=凭证导入导出.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'Book' object has no attribute 'loc'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[17], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;66;03m# 从凭证导入导出将数据写入到《凭证导入导出》\u001b[39;00m\n\u001b[0;32m      2\u001b[0m 凭证模版\u001b[38;5;241m.\u001b[39mrange(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124ma4:ab10000\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mclear_contents()\n\u001b[1;32m----> 3\u001b[0m 凭证模版\u001b[38;5;241m.\u001b[39mrange(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mh2:j2\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mvalue\u001b[38;5;241m=\u001b[39m凭证导入导出\u001b[38;5;241m.\u001b[39mloc[:,[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m科目编码\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m科目\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m币种\u001b[39m\u001b[38;5;124m'\u001b[39m]]\u001b[38;5;241m.\u001b[39mvalues  \n\u001b[0;32m      4\u001b[0m 凭证模版\u001b[38;5;241m.\u001b[39mrange(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mm2:o2\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mvalue\u001b[38;5;241m=\u001b[39m凭证导入导出\u001b[38;5;241m.\u001b[39mloc[:,[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m借贷方向\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m原币\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m本币\u001b[39m\u001b[38;5;124m'\u001b[39m]]\u001b[38;5;241m.\u001b[39mvalues\n\u001b[0;32m      5\u001b[0m 凭证模版\u001b[38;5;241m.\u001b[39mrange(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124my2:ab2\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mvalue\u001b[38;5;241m=\u001b[39m凭证导入导出\u001b[38;5;241m.\u001b[39mloc[:,[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m往来单位编码\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m往来单位\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m部门编码\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m部门\u001b[39m\u001b[38;5;124m'\u001b[39m]]\u001b[38;5;241m.\u001b[39mvalues\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'Book' object has no attribute 'loc'"
     ]
    }
   ],
   "source": [
    "# 从凭证导入导出将数据写入到《凭证导入导出》\n",
    "凭证模版.range('a4:ab10000').clear_contents()\n",
    "凭证模版.range('h2:j2').value=凭证导入导出.loc[:,['科目编码','科目','币种']].values  \n",
    "凭证模版.range('m2:o2').value=凭证导入导出.loc[:,['借贷方向','原币','本币']].values\n",
    "凭证模版.range('y2:ab2').value=凭证导入导出.loc[:,['往来单位编码','往来单位','部门编码','部门']].values\n",
    "凭证模版.range('g2').options(transpose=True).value= 凭证导入导出.loc[:,'备注'].values #摘要\n",
    "凭证模版.range('c2').options(transpose=True).value= 凭证导入导出.loc[:,'合并标识'].values #凭证号\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
